﻿Imports System.Data
Imports System.Data.SqlClient
Public Class BancosDAL
    Dim cmd As SqlCommand
#Region "Métodos"
    Function inserir(ByVal descricao As String, ByVal endereco As String, ByVal bairro As String, ByVal cidade As String, ByVal numagencia As String, ByVal telefone1 As Integer, ByVal telefone2 As Integer, ByVal cel As Integer, ByVal cep As Integer, ByVal uf As String, ByVal contato As String, ByVal homepage As String, ByVal endnum As String, ByVal bantipo As String, ByVal status As Integer) As Integer
        Dim cmd As SqlCommand
        Dim codigo As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPInsereBancos @codigo, @descricao, @endereco, @bairro, @cidade, @numagencia, @telefone1, @telefone2,@celular, @cep,@uf, @contato, @homepage, @endnumero, @bantipo, @status"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = 0
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = endereco
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = bairro
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@numagencia", SqlDbType.VarChar)).Value = numagencia
            cmd.Parameters.Add(New SqlParameter("@telefone1", SqlDbType.Int)).Value = telefone1
            cmd.Parameters.Add(New SqlParameter("@telefone2", SqlDbType.Int)).Value = telefone2
            cmd.Parameters.Add(New SqlParameter("@celular", SqlDbType.Int)).Value = cel
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.Int)).Value = cep
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = uf
            cmd.Parameters.Add(New SqlParameter("@contato", SqlDbType.VarChar)).Value = contato
            cmd.Parameters.Add(New SqlParameter("@homepage", SqlDbType.VarChar)).Value = homepage
            cmd.Parameters.Add(New SqlParameter("@bantipo", SqlDbType.VarChar)).Value = bantipo
            cmd.Parameters.Add(New SqlParameter("@endnumero", SqlDbType.VarChar)).Value = endnum
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            codigo = objconexao.executacmdAI(cmd, "Gen_Bancos")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
    Function alterar(ByVal descricao As String, ByVal endereco As String, ByVal bairro As String, ByVal cidade As String, ByVal numagencia As String, ByVal telefone1 As Integer, ByVal telefone2 As Integer, ByVal cel As Integer, ByVal cep As Integer, ByVal uf As String, ByVal contato As String, ByVal homepage As String, ByVal endnum As String, ByVal bantipo As String, ByVal status As Integer, ByRef codigo As Integer)
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPInsereBancos @codigo,@descricao, @endereco, @bairro, @cidade, @numagencia, @telefone1, @telefone2,@celular, @cep,@uf, @contato, @homepage, @endnumero, @bantipo, @status"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = endereco
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = bairro
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@numagencia", SqlDbType.VarChar)).Value = numagencia
            cmd.Parameters.Add(New SqlParameter("@telefone1", SqlDbType.Int)).Value = telefone1
            cmd.Parameters.Add(New SqlParameter("@telefone2", SqlDbType.Int)).Value = telefone2
            cmd.Parameters.Add(New SqlParameter("@celular", SqlDbType.Int)).Value = cel
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.Int)).Value = cep
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = uf
            cmd.Parameters.Add(New SqlParameter("@contato", SqlDbType.VarChar)).Value = contato
            cmd.Parameters.Add(New SqlParameter("@homepage", SqlDbType.VarChar)).Value = homepage
            cmd.Parameters.Add(New SqlParameter("@bantipo", SqlDbType.VarChar)).Value = bantipo
            cmd.Parameters.Add(New SqlParameter("@endnumero", SqlDbType.VarChar)).Value = endnum
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
#Region "Localizar Bancos - código"
    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Gen_Bancos where ban_codigo= @codigo and sta_codigo <> 3 order by ban_tipo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizar() As DataTable 'Sem parâmetro
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_GenBancos where sta_codigo <> 3 order by ban_descricao"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizar(ByVal nome As String) As DataTable 'por nome
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select ban_descricao, ban_agencia, sta_descricao from vw_GenBancos where ban_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @nome order by ban_descricao"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizarAgencia(ByVal agencia As String) As DataTable 'por agencia
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select ban_descricao, ban_agencia, sta_descricao from vw_GenBancos where ban_agencia COLLATE SQL_Latin1_General_CP1_CI_AI like @agencia order by ban_descricao"
            cmd.Parameters.Add(New SqlParameter("@agencia", SqlDbType.VarChar)).Value = agencia
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizarAgStatus(ByVal status As String) As DataTable 'por status
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select *  from vw_GenBancos where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @status order by sta_descricao"
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.VarChar)).Value = status
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Validação"
    Function ValidaReplica(ByVal codigo As Integer, ByVal descricao As String, ByVal numagencia As String) As Boolean
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Gen_Bancos where ban_descricao = @descricao and ban_agencia=@numagencia and sta_codigo <> 3 and ban_codigo <> @codigo;"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@numagencia", SqlDbType.VarChar)).Value = numagencia
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Valida Exclusão"
    Function validaExclusao(ByVal cod As Integer, ByVal stat As Integer) As Boolean
        Dim tab As DataTable
        Try

            If (stat <> 1) Then
                cmd = New SqlCommand
                cmd.CommandText = "select * from Financeiro.Ofi_Contas where ban_codigo = @codigo and sta_codigo <> 3 "
                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
                tab = objconexao.executaConsulta(cmd)

                If (tab.Rows.Count = 0) Then
                    Return True
                Else
                    Return False
                End If

            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return True
    End Function
#End Region
End Class
